package Oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

public class InsertUtil {
	
	static Connection conn = null;
	
	public InsertUtil(){
		getConn();
	}
	
	public static void getConn() {
		String url =  "jdbc:oracle:thin:@ (DESCRIPTION = (ADDRESS = (PROTOCOL =TCP)"
				+ "(HOST = 10.1.7.9)(PORT = 1521)) (LOAD_BALANCE = yes)(CONNECT_DATA = (SERVICE_NAME = tc) ) )";
			String username = "dataexchange";
			String pwd = "dataexchange123";
//			String username = "dec";
//			String pwd = "dec123";
			String driverClass = "oracle.jdbc.driver.OracleDriver";
			
			try {
				conn = DBConnecter.getConnection(driverClass, url, username, pwd);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}

	public static String getTaskCode(){
		UUID u = UUID.randomUUID();
		return u.toString();
	}
	
	public  void InsertTDOWNLOADTASK(String mainfolder,int id) throws SQLException{
		if(conn==null){
			getConn() ;
		}
        String taskid = getTaskCode();
		CHYIIIUploadTaskBean bean = new CHYIIIUploadTaskBean();
		Date date = new Date();
		bean.setCreateDate(date);
		bean.setCreater("tsproxy");
		bean.setDownloadCounts(0);
		bean.setFileCounts(1);
		bean.setFtpPath(mainfolder);
		bean.setId(id);
		bean.setLocalPath("NONE");
		bean.setMotorecycleType("A11");
		bean.setPurpose("在线测试");
		bean.setRecipient("ABCDEFtest8");//接收人
		bean.setRecipientDept("供应商有限公司");
		bean.setRemarks("NONE");
		bean.setSender("tsproxy");
		bean.setSenderDept("情报资料科");
		bean.setState(1);
		bean.setTaskCode(getTaskCode());
		bean.setTaskName("cheryTO供应商");
		uploadTask2DB(bean);
	}
	public  void insertTDOWNLOADDATALIST(List<String> filelists ,int id) throws SQLException{
		if(conn==null){
			getConn() ;
		}
		CHYIIIUploadFileBean filebean=null;
		Date date = new Date();
		List<CHYIIIUploadFileBean> uploadFileList = new ArrayList<CHYIIIUploadFileBean>();
		for(String remotepath:filelists){
			filebean	= new CHYIIIUploadFileBean();
			filebean.setTaskID(id);
			filebean.setFtpPath(remotepath);
			filebean.setLocalPath("D:\\x");
			filebean.setState("1");
			filebean.setCreateTime(date);
			filebean.setCreater("tsproxy");
			uploadFileList.add(filebean);
		}
		 
		uploadFile2DB(uploadFileList, conn);
	}
	
	public static void main(String[] args) throws SQLException {

	}

	public  void uploadFile2DB(List<CHYIIIUploadFileBean> list, Connection conn)
			throws SQLException {
		if(conn==null){
			getConn() ;
		}
		String sql = "insert into CHYIII_DEC_TDOWNLOADDATALIST"
				+ " (id,taskid,ftppath,localpath,state,createtime,createuser)"
				+ " values(S_dec.Nextval,?,?,?,?,to_date(?,'yyyy-mm-dd HH24:mi:ss'),?)";
		PreparedStatement pstat = null;
		int i = 0;
		pstat = conn.prepareStatement(sql);
		for (CHYIIIUploadFileBean bean : list) {
			pstat.setInt(1, bean.getTaskID());
			pstat.setString(2, bean.getFtpPath());
			pstat.setString(3, bean.getLocalPath());
			pstat.setString(4, bean.getState());
			// 时间字符串
			String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(bean.getCreateTime());
			pstat.setString(5, date);
			pstat.setString(6, bean.getCreater());
			pstat.addBatch();
			i++;
			if (i % 1000 == 0) {
				pstat.executeBatch();
				pstat.clearBatch();
			}
		}
		pstat.executeBatch();
		pstat.clearBatch();
	}

	public  void uploadTask2DB(CHYIIIUploadTaskBean bean )
			throws SQLException {
		if(conn==null){
			getConn() ;
		}
		String sql = "insert into CHYIII_DEC_TDOWNLOADTASK"
				+ " (id,taskcode,taskname,sender,recipient,motorcycletype,ftppath,purpose,state,createtime,"
				+ "createuser,senderdept,recipientdept,localpath,filecount,filedownloadedcount,remark)"
				+ " values(?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-mm-dd HH24:mi:ss'),?,?,?,?,?,?,?)";
		PreparedStatement pstat = null;
		pstat = conn.prepareStatement(sql);
		pstat.setInt(1, bean.getId());
		pstat.setString(2, bean.getTaskCode());
		pstat.setString(3, bean.getTaskName());
		pstat.setString(4, bean.getSender());
		pstat.setString(5, bean.getRecipient());
		pstat.setString(6, bean.getMotorecycleType());
		pstat.setString(7, bean.getFtpPath());
		pstat.setString(8, bean.getPurpose());
		pstat.setInt(9, bean.getState());
		// 时间字符串
		String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(bean
				.getCreateDate());
		pstat.setString(10, date);
		pstat.setString(11, bean.getCreater());
		pstat.setString(12, bean.getSenderDept());
		pstat.setString(13, bean.getRecipientDept());
		pstat.setString(14, bean.getLocalPath());
		pstat.setInt(15, bean.getFileCounts());
		pstat.setInt(16, bean.getDownloadCounts());
		pstat.setString(17, bean.getRemarks());
		pstat.execute();
	}

	public  int makeTaskID() {
		if(conn==null){
			getConn() ;
		}
		String sql = "select S_dec.Nextval from dual";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		int re=-1;
		try {
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				re = rs.getInt(1);
				return re;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null){
					rs.close();	
				}
				
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			if (pstat != null)
				try {
					pstat.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return re;
	}
}
